#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

ads_province_trade_count="
insert overwrite table ${APP}.ads_province_trade_count
select
    '$do_date' dt,
    1 recent_days,
    province_name,
    order_user_1d,
    order_count_1d,
    order_amount_1d
from ${APP}.dws_trade_province_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    province_name,
    case recent_days
        when 7 then order_user_7d
        when 30 then order_user_30d
    end order_user,
    case recent_days
        when 7 then order_count_7d
        when 30 then order_count_30d
    end order_count,
    case recent_days
        when 7 then order_amount_7d
        when 30 then order_amount_30d
    end order_amount
from ${APP}.dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_traffic_stats_by_source="
insert overwrite table ${APP}.ads_traffic_stats_by_source
select * from ${APP}.ads_traffic_stats_by_source
union
select
    '$do_date' dt,
    recent_days,
    source_site,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate
from ${APP}.dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('$do_date',-recent_days+1)
group by recent_days,source_site;
"
ads_page_path="
insert overwrite table ${APP}.ads_page_path
select * from ${APP}.ads_page_path
union
select
    '$do_date' dt,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            page_id,
            lead(page_id,1,null) over(partition by session_id order by view_time) next_page_id,
             row_number() over (partition by session_id order by view_time) rn
        from ${APP}.dwd_traffic_page_view_inc
        where dt='$do_date'
    )t1
)t2
group by source,target;
"
ads_user_change="
insert overwrite table ${APP}.ads_user_change
select *
from ${APP}.ads_user_change
union
select churn.dt,
       user_churn_count,
       user_back_count
from (
         select '$do_date' dt,
                count(*)     user_churn_count
         from ${APP}.dws_user_user_login_td
         where dt = '$do_date'
           and login_date_last = date_add('$do_date', -7)
     ) churn
         join
     (
         select '$do_date' dt,
                count(*)     user_back_count
         from (
                  select user_id,
                         login_date_last
                  from ${APP}.dws_user_user_login_td
                  where dt = '$do_date'
              ) t1
                  join
              (
                  select user_id,
                         login_date_last login_date_previous
                  from ${APP}.dws_user_user_login_td
                  where dt = date_add('$do_date', -1)
              ) t2
              on t1.user_id = t2.user_id
         where datediff(login_date_last, login_date_previous) >= 8
     ) back
     on churn.dt = back.dt;
"
ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
 select *
 from ${APP}.ads_user_retention
 union
select '$do_date'                                                                           dt,
       login_date_first                                                                       create_date,
       datediff('$do_date', login_date_first)                                               retention_day,
       sum(if(login_date_last = '$do_date', 1, 0))                                          retention_count,
       count(*)                                                                               new_user_count,
       cast(sum(if(login_date_last = '$do_date', 1, 0)) / count(*) * 100 as decimal(16, 2)) retention_rate
from (
         select user_id,
                date_id login_date_first
         from ${APP}.dwd_user_register_inc
         where dt >= date_add('$do_date', -7)
           and dt < '$do_date'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from ${APP}.dws_user_user_login_td
         where dt = '$do_date'
     ) t2
     on t1.user_id = t2.user_id
group by login_date_first;
"
ads_user_stats="
insert overwrite table ${APP}.ads_user_stats
select *
from ${APP}.ads_user_stats
union
select '$do_date' dt,
       t1.recent_days,
       new_user_count,
       active_user_count
from (
         select recent_days,
                sum(if(login_date_last >= date_add('$do_date', -recent_days + 1), 1, 0)) new_user_count
         from ${APP}.dws_user_user_login_td lateral view explode(array(1, 7, 30)) tmp as recent_days
         where dt = '$do_date'
         group by recent_days
     ) t1
         join
     (
         select recent_days,
                sum(if(date_id >= date_add('$do_date', -recent_days + 1), 1, 0)) active_user_count
         from ${APP}.dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
         group by recent_days
     ) t2
     on t1.recent_days = t2.recent_days;
"
ads_user_action="
insert overwrite table ${APP}.ads_user_action
select *
from ${APP}.ads_user_action
union
select '$do_date' dt,
       home_count,
       course_detail_count,
       cart_count,
       order_count,
       payment_count
from (
         select 1                                        recent_days,
                sum(if(page_id = 'home', 1, 0))          home_count,
                sum(if(page_id = 'course_detail', 1, 0)) course_detail_count
         from ${APP}.dws_traffic_page_visitor_page_view_1d
         where dt = '$do_date'
           and page_id in ('home', 'course_detail')
     ) page
         join
     (
         select 1        recent_days,
                count(*) cart_count
         from ${APP}.dws_trade_user_cart_add_1d
         where dt = '$do_date'
     ) cart
     on page.recent_days = cart.recent_days
         join
     (
         select 1                       recent_days,
                count(distinct user_id) order_count
         from ${APP}.dws_trade_user_course_order_1d
         where dt = '$do_date'
     ) ord
     on page.recent_days = ord.recent_days
         join
     (
         select 1        recent_days,
                count(*) payment_count
         from ${APP}.dws_trade_user_payment_1d
         where dt = '$do_date'
     ) pay
     on page.recent_days = pay.recent_days;
"
ads_new_order_user_stats="
insert overwrite table ${APP}.ads_new_order_user_stats
 select *
 from ${APP}.ads_new_order_user_stats
 union
select '$do_date' dt,
       t1.recent_days,
       t1.new_order_user_count,
       t2.new_pyment_user_count
from ((select recent_days,
              sum(if(order_date_first >= date_add('$do_date', -recent_days + 1), 1, 0)) new_order_user_count
       from ${APP}.dws_trade_user_order_td lateral view explode(array(1, 7, 30)) tmp as recent_days
       where dt = '$do_date'
       group by recent_days
      ) t1
         join(
    select 1                       recent_days,
           count(distinct user_id) new_pyment_user_count
    from ${APP}.dws_trade_user_payment_1d
    where dt = '$do_date'
    union
    select 7                       recent_days,
           count(distinct user_id) new_pyment_user_count
    from ${APP}.dws_trade_user_payment_1d
    where dt >= date_add('$do_date', -6)
      and dt <= '$do_date'
    union
    select 30                      recent_days,
           count(distinct user_id) new_pyment_user_count
    from ${APP}.dws_trade_user_payment_1d
    where dt >= date_add('$do_date', -29)
      and dt <= '$do_date'
) t2
             on t1.recent_days = t2.recent_days);
"
ads_age_order_user_count="
with
tmp as
    (
    select
           dt,
           user_id,
             case
                 when age > 0 and age <= 18 then "0-18"
                 when age > 18 and age <= 40 then "19-40"
                 when age > 40 and age <= 60 then "40-60"
                 else '60以上'
                 end age_groups
      from ${APP}.dws_trade_user_course_order_1d
      where dt = '$do_date'
    )
insert overwrite table ${APP}.ads_age_order_user_count
select *
from ${APP}.ads_age_order_user_count
union
select '$do_date'            dt,
       1                       recent_days,
       age_groups,
       count(distinct user_id) order_user_count
from tmp
where dt = '$do_date'
group by age_groups
union
select '$do_date'            dt,
       7                       recent_days,
       age_groups,
       count(distinct user_id) order_user_count
from tmp
where dt >= date_add('$do_date', -6)
  and dt <= '$do_date'
group by age_groups
union
select '$do_date'            dt,
       30                      recent_days,
       age_groups,
       count(distinct user_id) order_user_count
from tmp
where dt >= date_add('$do_date', -29)
  and dt <= '$do_date'
group by age_groups;
"
ads_course_firstplay_retention_count="
with tmp
as
(select
        play.user_id            user_id,
        play.course_name          course_name,
        substr(play.watch_date_first,0,10)   watch_date_first,
        ord.dt                  dt
from
(select
    user_id,
    watch_date_first,
    course_name
from ${APP}.dws_course_user_watch_course_td
 where dt > date_add('$do_date', -7)
       and dt <= '$do_date'
) play
left join
(select
    dt,
    course_name,
    user_id
from ${APP}.dws_trade_user_course_order_1d
where dt ='$do_date'
) ord
on ord.user_id = play.user_id)
insert overwrite table ${APP}.ads_course_firstplay_retention_count
 select *
 from ${APP}.ads_course_firstplay_retention_count
 union
select
     '$do_date' dt,
     watch_date_first,
     datediff('$do_date',STRING(watch_date_first) )+1 retention_day,
    course_name,
    count(*) firstplay_user_count,
    cast(sum(if(dt is null,0,1))/count(*)  * 100 as decimal(16, 2)) firstplay_retention_rate
from tmp
group by course_name,watch_date_first;
"
ads_subject_firstplay_retention_count="
with tmp
as
(select
        play.user_id            user_id,
        play.subject_name          subject_name,
        substr(play.watch_date_first,0,10)   watch_date_first,
        ord.dt                  dt
from
(select
    user_id,
    watch_date_first,
    subject_name
from ${APP}.dws_course_user_watch_course_td
 where dt > date_add('$do_date', -7)
       and dt <= '$do_date'
) play
left join
(select
    dt,
    subject_name,
    user_id
from ${APP}.dws_trade_user_course_order_1d
where dt ='$do_date'
) ord
on ord.user_id = play.user_id)
insert overwrite table ${APP}.ads_subject_firstplay_retention_count
 select *
 from ${APP}.ads_subject_firstplay_retention_count
 union
select
     '$do_date' dt,
     watch_date_first,
     datediff('$do_date',STRING(watch_date_first) )+1 retention_day,
    subject_name,
    count(*) firstplay_user_count,
    cast(sum(if(dt is null,0,1))/count(*)  * 100 as decimal(16, 2)) firstplay_retention_rate
from tmp
group by subject_name,watch_date_first;
"
ads_category_firstplay_retention_count="
with tmp
as
(select
        play.user_id            user_id,
        play.category_name        category_name ,
        substr(play.watch_date_first,0,10)   watch_date_first,
        ord.dt                  dt
from
(select
    user_id,
    watch_date_first,
    category_name
from ${APP}.dws_course_user_watch_course_td
 where dt > date_add('$do_date', -7)
       and dt <= '$do_date'
) play
left join
(select
    dt,
    category_name ,
    user_id
from ${APP}.dws_trade_user_course_order_1d
where dt ='$do_date'
) ord
on ord.user_id = play.user_id)
insert overwrite table ${APP}.ads_category_firstplay_retention_count
 select *
 from ${APP}.ads_category_firstplay_retention_count
 union
select
     '$do_date' dt,
     watch_date_first,
     datediff('$do_date',STRING(watch_date_first) )+1 retention_day,
    category_name ,
    count(*) firstplay_user_count,
    cast(sum(if(dt is null,0,1))/count(*)  * 100 as decimal(16, 2)) firstplay_retention_rate
from tmp
group by category_name ,watch_date_first;
"
ads_course_trade_count="
insert overwrite table ${APP}.ads_course_trade_count
select dt,if(dt >= date_add('$do_date',+0),1,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-6),7,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-29),30,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by dt,course_name;
"
ads_course_comment_count="
insert overwrite table ${APP}.ads_course_comment_count
select
    dt,
    if(dt >= date_add('$do_date',+0),1,0) as recent_days,
    course_name,
    avg(comment_1d),
    count(user_id) ,
    (sum(if (star_comment_1d = 'TRUE', 1 , 0) ) / count(user_id))
from ${APP}.dws_course_user_comment_1d
where dt >= date_add('$do_date',+0) and dt <= date_add('$do_date',-0)
group by course_name, dt
union
select
    dt,
    if(dt >= date_add('$do_date',-6),7,0) as recent_days,
    course_name,
    avg(comment_1d),
    count(user_id) ,
    (sum(if (star_comment_1d = 'TRUE', 1 , 0) ) / count(user_id))
from ${APP}.dws_course_user_comment_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by course_name, dt
union
select
    dt,
    if(dt <= date_add('$do_date',+29),30,0) as recent_days,
    course_name,
    avg(comment_1d),
    count(user_id) ,
    (sum(if (star_comment_1d = 'TRUE', 1 , 0) ) / count(user_id))
from ${APP}.dws_course_user_comment_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by course_name, dt;
"
ads_chapter_video_play_count="
insert overwrite table ${APP}.ads_chapter_video_play_count
select
    dt,
    if(dt >= date_add('$do_date',-0),1,0) as recent_days,
    chapter_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by chapter_name,dt
union
select
    dt,
    if(dt >= date_add('$do_date',-6),7,0) as recent_days,
    chapter_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by chapter_name,dt
union
select
    dt,
    if(dt >= date_add('$do_date',-29),30,0) as recent_days,
    chapter_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by chapter_name,dt;
"
ads_cource_video_play_count="
insert overwrite table ${APP}.ads_cource_video_play_count
select
    dt,
    if(dt >= date_add('$do_date',-0),1,0) as recent_days,
    course_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by course_name,dt
union
select
    dt,
    if(dt <= date_add('$do_date',-6),7,0) as recent_days,
    course_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt <= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by course_name,dt
union
select
    dt,
    if(dt <= date_add('$do_date',-29),30,0) as recent_days,
    course_name,
    sum(play_count_1d),
    avg(play_time_1d),
    sum(watch_user_count_1d)
from
${APP}.dws_action_video_watch_1d
where dt <= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by course_name,dt;
"
ads_cource_end_user_count="
insert overwrite table ${APP}.ads_cource_end_user_count
select
    dt,
    if(dt >= date_add('$do_date',-0),1,0) as recent_days,
    course_name,
    sum(watch_end_user_count_1d)
from
${APP}.dws_action_chapter_end_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by course_name,dt

union
select
    dt,
    if(dt >= date_add('$do_date',-6),7,0) as recent_days,
    course_name,
    sum(watch_end_user_count_1d)
from
${APP}.dws_action_chapter_end_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by course_name,dt
union
select
    dt,
    if(dt <= date_add('$do_date',+29),7,0) as recent_days,
    course_name,
    sum(watch_end_user_count_1d)
from
${APP}.dws_action_chapter_end_1d
where dt <= date_add('$do_date',+29) and dt <= date_add('$do_date',-0)
group by course_name,dt;
"
ads_cource_end_combined="
insert overwrite table ${APP}.ads_cource_end_combined
select  max(dt) dt,recent_days,sum(a.sum1) cource_end_user_count,sum(a.sum2) cource_end_count
from
(select dt,
       if(dt = date_add('$do_date',-0),1,0) as recent_days,
       sum(course_end_user_count_1d) sum1,
       sum(course_end_count_1d) sum2
from
${APP}.dws_action_course_end_1d
where dt = date_add('$do_date',-0)
group by dt) as a
group by recent_days


union
select  max(dt) dt,recent_days,sum(a.sum1) cource_end_user_count,sum(a.sum2) cource_end_count
from
(select dt,
       if(dt >= date_add('$do_date',-6),7,0) as recent_days,
       sum(course_end_user_count_1d) sum1,
       sum(course_end_count_1d) sum2
from
${APP}.dws_action_course_end_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by dt) as a
group by recent_days

union

select  max(dt) dt,recent_days,sum(a.sum1) cource_end_user_count,sum(a.sum2) cource_end_count
from
(select dt,
       if(dt >= date_add('$do_date',-29),30,0) as recent_days,
       sum(course_end_user_count_1d) sum1,
       sum(course_end_count_1d) sum2
from
${APP}.dws_action_course_end_1d
where dt >= date_add('$do_date',-29)and dt <= date_add('$do_date',-0)
group by dt) as a
group by recent_days;
"
ads_cource_user_avg_end_chapter_video_count="
insert overwrite table ${APP}.ads_cource_user_avg_end_chapter_video_count
select
        dt,
       if(dt = date_add('$do_date',-0),1,0) as recent_days,
       course_name,
       watch_end_count_1d/watch_end_user_count_1d
from
${APP}.dws_action_chapter_end_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by dt,course_name,watch_end_count_1d,watch_end_user_count_1d
union
select
        dt,
       if(dt >= date_add('$do_date',-6),7,0) as recent_days,
       course_name,
       watch_end_count_1d/watch_end_user_count_1d
from
${APP}.dws_action_chapter_end_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by dt,course_name,watch_end_count_1d,watch_end_user_count_1d
union
select
        dt,
       if(dt >= date_add('$do_date',-29),30,0) as recent_days,
       course_name,
       watch_end_count_1d/watch_end_user_count_1d
from
${APP}.dws_action_chapter_end_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by dt,course_name,watch_end_count_1d,watch_end_user_count_1d;
"
ads_sourse_order="
with dpo as (
    select
        dpv.order_id as order_id,
        dpv.user_id as user_id,
        dpv.channel as channel,
        dpv.v_dt as v_dt,
        order_total_amount
    from (
        select
            if(page_item_type='order_id',page_item,null) as order_id,
            user_id,
            channel,
            dt as v_dt
        from ${APP}.dwd_traffic_page_view_inc
        where dt>=date_add('$do_date',-29)
    ) dpv left join (
        select
            order_id,
            user_id,
            sum(total_amount) as order_total_amount
        from ${APP}.dwd_trade_order_info_inc
        where dt>=date_add('$do_date',-29) and payment_status='1602' and final_amount IS NOT NULL
        group by order_id, user_id
    ) doi on dpv.order_id=doi.order_id and dpv.user_id=doi.user_id )
insert overwrite table ${APP}.ads_sourse_order
( select * from ${APP}.ads_sourse_order )
union(
select
    '$do_date' as dt,
    CAST(recent_days as BIGINT),
    channel as channel,
    cast(sum(order_total_amount) as DECIMAL(16, 2)) as sale_amount,
    cast(100* size(collect_set(if(order_total_amount is null, null, user_id)))
             / size(collect_set(user_id)) as DECIMAL(16, 2)) as conversion_rate
from dpo lateral view explode(array(1,7,30)) dpo as recent_days
where v_dt>=date_add('$do_date',-recent_days+1) and v_dt<date_add('$do_date',1)
group by recent_days,channel
);
"
ads_category_course_trade_count="
insert overwrite table ${APP}.ads_category_course_trade_count

select a.dt,a.recent_days,b.category_name,sum(a.c1),sum(a.c2),sum(a.c3)
from ${APP}.dim_course_info_full b  join

(select dt,if(dt >= date_add('$do_date',+0),1,0) as recent_days,course_name,count(user_id) c1,count(distinct user_id) c2,sum(order_amount_1d) c3
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-6),7,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-29),30,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by dt,course_name) a

on a.course_name  = b.course_name
group by a.dt,a.recent_days,b.category_name;
"
ads_subjet_course_trade_count="
insert overwrite table ${APP}.ads_subjet_course_trade_count

select a.dt,a.recent_days,b.subject_name,sum(a.c1),sum(a.c2),sum(a.c3)
from ${APP}.dim_course_info_full b  join

(select dt,if(dt >= date_add('$do_date',+0),1,0) as recent_days,course_name,count(user_id) c1,count(distinct user_id) c2,sum(order_amount_1d) c3
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-0) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-6),7,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-6) and dt <= date_add('$do_date',-0)
group by dt,course_name
union
select dt,if(dt >= date_add('$do_date',-29),30,0) as recent_days,course_name,count(user_id),count(distinct user_id),sum(order_amount_1d)
from
${APP}.dws_trade_user_course_order_1d
where dt >= date_add('$do_date',-29) and dt <= date_add('$do_date',-0)
group by dt,course_name) a

on a.course_name  = b.course_name
group by a.dt,a.recent_days,b.subject_name;
"
ads_question_right_rate="
insert overwrite table ${APP}.ads_question_right_rate
( select * from ${APP}.ads_question_right_rate )
union (
select
    '$do_date' as dt,
    CAST(dn.recent_days as BIGINT) as recent_days,
    oqi.question as question,
    CAST(dn.right_rate as DECIMAL(16,2)) as right_rate
from ((
    select
        question_id,
        CAST(30 as BIGINT) as recent_days,
        100* sum(is_correct_count_1d) / sum(question_count_1d) as right_rate
    from ${APP}.dws_action_question_1d
    where dt>=date_add('$do_date',-29)
    GROUP BY question_id
 )union(
    select
        question_id,
        CAST(7 as BIGINT) as recent_days,
        100* sum(is_correct_count_1d) / sum(question_count_1d) as right_rate
    from ${APP}.dws_action_question_1d
    where dt>=date_add('$do_date',-6)
    GROUP BY question_id
 )union (
    select
        question_id,
        CAST(1 as BIGINT) as recent_days,
        100* sum(is_correct_count_1d) / sum(question_count_1d) as right_rate
    from ${APP}.dws_action_question_1d
    WHERE dt='$do_date'
    GROUP BY question_id
)) dn
left join (
    select
        id,
        if(min(question_txt) is not null, min(question_txt), cast(id as STRING)) as question
    from ${APP}.ods_test_question_info_full
    group by id
) oqi on dn.question_id=oqi.id
);
"
ads_exampaper_combined_count="
insert overwrite table ${APP}.ads_exampaper_combined_count
(select * from ${APP}.ads_exampaper_combined_count)
union(
select
    '$do_date' as dt,
    CAST(dn.recent_days as BIGINT) as recent_days,
    dpi.exampaper as exampaper,
    CAST(dn.avg_score as DECIMAL(16,2)) as avg_score,
    CAST(dn.avg_time as DECIMAL(16,2)) as avg_time,
    dn.user_count as user_count
from((
    select
        paper_id,
        CAST(30 as BIGINT) as recent_days,
        sum(score_sum_30d) / sum(times_count_30d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_30d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_nd
    WHERE dt>=date_add('$do_date',-29)
    GROUP BY paper_id
) union(
    select
        paper_id,
        CAST(7 as BIGINT) as recent_days,
        sum(score_sum_7d) / sum(times_count_7d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_7d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_nd
    WHERE dt>=date_add('$do_date',-6)
    GROUP BY paper_id
) union (
    select
        paper_id,
        CAST(1 as BIGINT) as recent_days,
        sum(score_sum_1d) / sum(times_count_1d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_1d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_1d
    WHERE dt='$do_date'
    GROUP BY paper_id
)) dn
left join (
    select
        id,
        if(min(paper_title) is not null, min(paper_title), cast(id as STRING)) as exampaper
    from ${APP}.dim_paper_info_full
    group by id
) dpi on dn.paper_id=dpi.id
);
"
ads_course_exam_count="
insert overwrite table ${APP}.ads_course_exam_count
(select * from ${APP}.ads_course_exam_count)
union(
select
    '$do_date' as dt,
    CAST(dn.recent_days as BIGINT) as recent_days,
    CAST(dci.course as STRING) as course,
    CAST(dn.avg_score as DECIMAL(16,2)) as avg_score,
    CAST(dn.avg_time as DECIMAL(16,2)) as avg_time,
    dn.user_count as user_count
from((
    select
        course_id,
        CAST(30 as BIGINT) as recent_days,
        sum(score_sum_30d) / sum(times_count_30d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_30d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_nd
    WHERE dt>=date_add('$do_date',-29)
    GROUP BY course_id
) union(
    select
        course_id,
        CAST(7 as BIGINT) as recent_days,
        sum(score_sum_7d) / sum(times_count_7d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_7d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_nd
    WHERE dt>=date_add('$do_date',-6)
    GROUP BY course_id
) union (
    select
        course_id,
        CAST(1 as BIGINT) as recent_days,
        sum(score_sum_1d) / sum(times_count_1d) as avg_score,
        count(DISTINCT user_id) as user_count,
        sum(duration_sum_1d) / count(DISTINCT user_id) as avg_time
    from ${APP}.dws_action_paper_1d
    WHERE dt='$do_date'
    GROUP BY course_id
)) dn
left join (
    select
        id,
        if(min(course_name) is not null, min(course_name), cast(id as STRING)) as course
    from ${APP}.dim_course_info_full
    group by id
) dci on dn.course_id=dci.id
);
"
ads_exampaper_score_count="
with datx as (
    select
        dt,
        paper_id,
        user_id,
        if(score<60, '[00,60)', if(score<70, '[60,70)', if(score<80, '[70,80)',
                if(score<90, '[80,90)',if(score<=100, '[90,100]', '(100,)'))
            ))) as score_groups
    from ${APP}.dwd_action_test_inc
    WHERE dt>=date_add('$do_date',-29) and score IS NOT NULL
)
insert overwrite table ${APP}.ads_exampaper_score_count
(select * from ${APP}.ads_exampaper_score_count)
union(
select
    '$do_date' as dt,
    dpi.exampaper as exampaper,
    CAST(dn.recent_days as BIGINT) as recent_days,
    dn.score_groups as score_groups,
    dn.ser_count as ser_count
from ((
    select
        CAST(30 as BIGINT) as recent_days,
        paper_id,
        score_groups,
        count(DISTINCT user_id) as ser_count
    from datx
    GROUP BY paper_id, score_groups

) union (
    select
        CAST(7 as BIGINT) as recent_days,
        paper_id,
        score_groups,
        count(DISTINCT user_id) as ser_count
    from datx
    WHERE dt>=date_add('$do_date',-6)
    GROUP BY paper_id, score_groups

) union (
    select
        CAST(1 as BIGINT) as recent_days,
        paper_id,
        score_groups,
        count(DISTINCT user_id) as ser_count
    from datx
    WHERE dt='$do_date'
    GROUP BY paper_id, score_groups

)) dn
left join (
    select
        id,
        if(min(paper_title) is not null, min(paper_title), cast(id as STRING)) as exampaper
    from ${APP}.dim_paper_info_full
    group by id
) dpi on dn.paper_id=dpi.id
);
"
ads_trade_combined_count="
insert overwrite table ${APP}.ads_trade_combined_count
(select * from ${APP}.ads_trade_combined_count)
union(
select
    '$do_date' as dt,
    recent_days,
    order_amount_count,
    order_count,
    order_user_count
from ((
    select CAST(30 as BIGINT) as recent_days,
        CAST(sum(order_amount_30d) as DECIMAL(16, 2)) as order_amount_count,
        CAST(sum(order_count_30d) as BIGINT) as order_count,
        count(DISTINCT user_id) as order_user_count
    from ${APP}.dws_trade_user_course_order_nd
    WHERE dt>=date_add('$do_date',-29)
) union(
    select
        CAST(7 as BIGINT) as recent_days,
        CAST(sum(order_amount_7d) as DECIMAL(16, 2)) as order_amount_count,
        CAST(sum(CAST(order_count_7d as BIGINT)) as BIGINT) as order_count,
        count(DISTINCT user_id) as order_user_count
    from ${APP}.dws_trade_user_course_order_nd
    WHERE dt>=date_add('$do_date',-6)
) union(
    select
        CAST(1 as BIGINT) as recent_days,
        CAST(sum(order_amount_1d) as DECIMAL(16, 2)) as order_amount_count,
        CAST(sum(order_count_1d) as BIGINT) as order_count,
        count(DISTINCT user_id) as order_user_count
    from ${APP}.dws_trade_user_course_order_1d
    WHERE dt='$do_date'
)) d1 LEFT JOIN (select '1' as dt, '2' as p2) t1 on d1.recent_days=t1.dt
);
"

case $1 in
    "ads_province_trade_count" )
        hive -e "$ads_province_trade_count"
    ;;
    "ads_traffic_stats_by_source" )
        hive -e "$ads_traffic_stats_by_source"
    ;; 
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;; 
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;; 
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;; 
    "ads_user_stats" )
        hive -e "$ads_user_stats"
    ;; 
    "ads_user_action" )
        hive -e "$ads_user_action"
    ;; 
    "ads_new_order_user_stats" )
        hive -e "$ads_new_order_user_stats"
    ;; 
    "ads_age_order_user_count" )
        hive -e "$ads_age_order_user_count"
    ;; 
    "ads_course_firstplay_retention_count" )
        hive -e "$ads_course_firstplay_retention_count"
    ;; 
    "ads_subject_firstplay_retention_count" )
        hive -e "$ads_subject_firstplay_retention_count"
    ;; 
    "ads_category_firstplay_retention_count" )
        hive -e "$ads_category_firstplay_retention_count"
    ;; 
    "ads_course_trade_count" )
        hive -e "$ads_course_trade_count"
    ;; 
    "ads_course_comment_count" )
        hive -e "$ads_course_comment_count"
    ;; 
    "ads_chapter_video_play_count" )
        hive -e "$ads_chapter_video_play_count"
    ;; 
    "ads_cource_video_play_count" )
        hive -e "$ads_cource_video_play_count"
    ;; 
    "ads_cource_end_user_count" )
        hive -e "$ads_cource_end_user_count"
    ;; 
    "ads_cource_end_combined" )
        hive -e "$ads_cource_end_combined"
    ;; 
    "ads_cource_user_avg_end_chapter_video_count" )
        hive -e "$ads_cource_user_avg_end_chapter_video_count"
    ;; 
    "ads_sourse_order" )
        hive -e "$ads_sourse_order"
    ;; 
    "ads_category_course_trade_count" )
        hive -e "$ads_category_course_trade_count"
    ;; 
    "ads_subjet_course_trade_count" )
        hive -e "$ads_subjet_course_trade_count"
    ;; 
    "ads_question_right_rate" )
        hive -e "$ads_question_right_rate"
    ;; 
    "ads_exampaper_combined_count" )
        hive -e "$ads_exampaper_combined_count"
    ;; 
    "ads_course_exam_count" )
        hive -e "$ads_course_exam_count"
    ;; 
    "ads_exampaper_score_count" )
        hive -e "$ads_exampaper_score_count"
    ;; 
    "ads_trade_combined_count" )
        hive -e "$ads_trade_combined_count"
    ;; 
    "all" )
        hive -e "$ads_province_trade_count$ads_traffic_stats_by_source$ads_page_path$ads_user_change$ads_user_retention$ads_user_stats$ads_user_action$ads_new_order_user_stats$ads_age_order_user_count$ads_course_firstplay_retention_count$ads_subject_firstplay_retention_count$ads_category_firstplay_retention_count$ads_course_trade_count$ads_course_comment_count$ads_chapter_video_play_count$ads_cource_video_play_count$ads_cource_end_user_count$ads_cource_end_combined$ads_cource_user_avg_end_chapter_video_count$ads_sourse_order$ads_category_course_trade_count$ads_subjet_course_trade_count$ads_question_right_rate$ads_exampaper_combined_count$ads_course_exam_count$ads_exampaper_score_count$ads_trade_combined_count"
    ;;
esac
